﻿CREATE PROCEDURE [dbo].[Report_CategorySameStoreComparison_Combos]
	  @RangeReviewId int
	 , @CategoryId smallint
	 , @ExchangeRate smallint = 0
	 
AS

--Report_CategorySameStoreComparison_Combos 1, 44

declare @startDatePY DATE = 
	(
		select CASE WHEN HasPriorYear = 1 THEN DATEADD(Month, -12, FirstMonth) ELSE FirstMonth END
		from RangeReviews where Id = @RangeReviewId
	)
declare @startDate DATE = (select FirstMonth from RangeReviews where Id = @RangeReviewId)
declare @totalMonths INT = (select TotalMonths from RangeReviews where Id = @RangeReviewId)
declare @endDate DATE = DATEADD(Month, @totalMonths - 1, @startDate)
declare @totalStores INT = (Select Count(StoreId) from RangeReviewStores where RangeReviewId = @RangeReviewId and IsExcluded = 0)
declare @totalCoverage INT = @totalStores * @totalMonths

begin try drop table #exchangeRates; end try begin catch end catch;

Select 
	months.Date
	, CASE 
		WHEN @ExchangeRate = 0 THEN 1 --Moneda Local
		WHEN @ExchangeRate = 1 THEN ISNULL(ex.Rate,1) --Rate para Average
		WHEN @ExchangeRate = 2 THEN ISNULL(ex.ForexRate,1) --Rate para Same Store
		ELSE 1
	END AS Rate
Into #exchangeRates
From Months months
INNER JOIN dbo.RangeReviews rr WITH (NOLOCK) on rr.Id = @RangeReviewId
INNER JOIN dbo.Currencies cur WITH (NOLOCK) on rr.CountryId = cur.CountryId
LEFT OUTER JOIN dbo.CurrencyExchangeRates ex WITH (NOLOCK) on ex.Date = months.Date and ex.SourceCurrencyId = cur.Id and ex.TargetCurrencyId = rr.TargetCurrencyId
Where months.Date BETWEEN @startDate AND @endDate

Select
	'General' Name
	, Data.Title
	, 0 CategoryId
	, '' Category
	, CASE 
		WHEN Data.Title = 'Single' THEN SUM(Data.Units) / CAST(@totalCoverage AS MONEY)
		ELSE SUM(Data.UnitsInCombos) / CAST(@totalCoverage AS MONEY)
	  END AS Units
	, CASE 
		WHEN Data.Title = 'Single' THEN SUM(Data.Sales) / CAST(@totalCoverage AS MONEY)
		ELSE SUM(Data.SalesInCombos) / CAST(@totalCoverage AS MONEY)
	  END AS Sales
	 , CASE 
		WHEN Data.Title = 'Single' THEN SUM(Data.GM) / CAST(@totalCoverage AS MONEY)
		ELSE SUM(Data.GMInCombos) / CAST(@totalCoverage AS MONEY)
	  END AS GM
	, CAST(0 AS MONEY) UnitsShare
	, CAST(0 AS MONEY) SalesShare
	, CAST(0 AS MONEY) GMShare
	, CAST(0 AS MONEY) PIDiff
	, CAST(0 AS MONEY) ChargeOffs
	, CAST(0 AS MONEY) InventoryCost
	, CAST(0 AS MONEY) LinearSpace
	, SKUs = COUNT(Data.BonusBuyNumber)
	, 0 Sales80
	, CAST(0 AS BIT) IsPriorYear
From
(
	Select 
		sales.Title
		, sales.BonusBuyNumber
		, CASE WHEN umcvalue IS NOT NULL THEN SUM(CAST(Units as money) / umcvalue) ELSE SUM(CAST(Units as money)) END AS Units
		, SUM(UnitsInCombos) UnitsInCombos
		, SUM(Sales) Sales
		, SUM(SalesInCombos) SalesInCombos
		, SUM(GM) * ISNULL(TMargin,1) GM
		, SUM(GMInCombos) * ISNULL(TMargin,1) GMInCombos
	From (
	Select 
		sales.BonusBuyNumber
		, umc.Value umcvalue
		, CASE 
			WHEN am.Margin is not null THEN am.Margin
			WHEN scm.Margin is not null THEN scm.Margin
			WHEN cm.Margin is not null THEN cm.Margin
		  ELSE NULL END AS TMargin
		, CASE WHEN sales.BonusBuyNumber IS NULL OR sales.BonusBuyNumber = '' OR sales.BonusBuyNumber = '#' OR sales.BonusBuyNumber = 'Not Assigned' THEN 'Single' ELSE 'Combo' END AS Title
		, CASE WHEN sales.BonusBuyNumber IS NULL OR sales.BonusBuyNumber = '' OR sales.BonusBuyNumber = '#' OR sales.BonusBuyNumber = 'Not Assigned' THEN SUM(Units) ELSE 0 END AS Units
		, CASE WHEN sales.BonusBuyNumber IS NOT NULL AND sales.BonusBuyNumber != '' AND sales.BonusBuyNumber != '#' AND sales.BonusBuyNumber != 'Not Assigned' THEN SUM(Units) ELSE 0 END AS UnitsInCombos 
		, CASE WHEN sales.BonusBuyNumber IS NULL OR sales.BonusBuyNumber = '' OR sales.BonusBuyNumber = '#' OR sales.BonusBuyNumber = 'Not Assigned' THEN SUM(Sales * ex.Rate) ELSE 0 END AS Sales
		, CASE WHEN sales.BonusBuyNumber IS NOT NULL AND sales.BonusBuyNumber != '' AND sales.BonusBuyNumber != '#' AND sales.BonusBuyNumber != 'Not Assigned' THEN SUM(Sales * ex.Rate) ELSE 0 END AS SalesInCombos 
		, CASE WHEN sales.BonusBuyNumber IS NULL OR sales.BonusBuyNumber = '' OR sales.BonusBuyNumber = '#' OR sales.BonusBuyNumber = 'Not Assigned' THEN SUM(GM * ex.Rate) ELSE 0 END AS GM
		, CASE WHEN sales.BonusBuyNumber IS NOT NULL AND sales.BonusBuyNumber != '' AND sales.BonusBuyNumber != '#' AND sales.BonusBuyNumber != 'Not Assigned' THEN SUM(GM * ex.Rate) ELSE 0 END AS GMInCombos 
		, CASE WHEN sales.BonusBuyNumber IS NULL OR sales.BonusBuyNumber = '' OR sales.BonusBuyNumber = '#' OR sales.BonusBuyNumber = 'Not Assigned' THEN ar.IsDeletedRangeReview ELSE 0 END AS IsDeletedRangeReview
	From ArticleSales sales WITH (NOLOCK)
	Inner Join Articles ar WITH (NOLOCK) on sales.ArticleId = ar.Id and ar.RangeReviewId = @RangeReviewId
	INNER JOIN RangeReviews rr WITH (NOLOCK) on ar.RangeReviewId = rr.Id
	INNER JOIN Currencies cur WITH (NOLOCK) on rr.CountryId = cur.CountryId
	INNER JOIN Countries co WITH (NOLOCK) on rr.CountryId = co.Id
	INNER JOIN Categories cat WITH (NOLOCK) on ar.CategoryId = cat.Id 
	--LEFT OUTER JOIN CurrencyExchangeRates ex WITH (NOLOCK) on ex.Date = sales.Date and ex.SourceCurrencyId = cur.Id and ex.TargetCurrencyId = rr.TargetCurrencyId
	INNER JOIN #exchangeRates ex on Sales.Date = ex.Date
	LEFT OUTER JOIN UnitOfMeasureConvertions umc WITH (NOLOCK) on ar.UnitOfMeasureConvertionId = umc.Id and ar.UOM = umc.SourceUnit
	LEFT JOIN TheoreticalMargins am WITH (NOLOCK)  on am.CountryId = rr.CountryId AND  am.ArticleId = ar.Id  
	LEFT JOIN TheoreticalMargins scm WITH (NOLOCK) on scm.CountryId = rr.CountryId AND (scm.ArticleId is null AND scm.CategoryId = cat.Id) 
	LEFT JOIN TheoreticalMargins cm WITH (NOLOCK)  on cm.CountryId = rr.CountryId AND (cm.ArticleId is null AND cm.CategoryId <> cat.Id AND cm.CategoryId = cat.ParentId)
	Where 
		(sales.Date BETWEEN @startDate AND @endDate)
		AND sales.Confirmed = 1 
		AND ar.IsDeletedPerformance = 0
		AND sales.StoreId in (select StoreId from RangeReviewStores where RangeReviewId = @RangeReviewId and IsExcluded = 0)
		and (@CategoryId = 0 OR ar.CategoryId = @CategoryId OR cat.ParentId = @CategoryId) 
	Group By
		sales.BonusBuyNumber
		, ar.IsDeletedRangeReview
		, umc.Value
		, am.Margin
		, scm.Margin
		, cm.Margin
	) sales
Where IsDeletedRangeReview = 0
Group By
	sales.BonusBuyNumber
	, sales.umcvalue
	, sales.TMargin
	, sales.Title
) Data
Group by 
	Data.Title


DROP TABLE #exchangeRates

